﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using System.Data.OleDb;
using System.Web.UI.WebControls;
using DataAccess.Object;
using Business;
using DevExpress.XtraGrid;
using DevExpress.XtraGrid.Views.Grid;

namespace Delivery.Controller.UserControl
{
    public partial class ucSanPhamImport : DevExpress.XtraEditors.XtraUserControl
    {
        private SanPhamBUS spBUS = new SanPhamBUS();
        private int rowSelect;
        string connectionString;

        public ucSanPhamImport()
        {
            InitializeComponent();
        }



        public DataTable getData(string filename)
        {
            OleDbConnection con;
            bool isOpenXMLFormat = false;
            if (isOpenXMLFormat)
                //read a 2007 file  
                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                    filename + ";Extended Properties=\"Excel 8.0;HDR=YES;\"";
            else
                //read a 97-2003 file  
                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                    filename + ";Extended Properties=Excel 8.0;";

            con = new OleDbConnection(connectionString);
            con.Open();

            //get all the available sheets  
            System.Data.DataTable dataSet = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            DataTable dataTable = new DataTable();

            if (dataTable.Rows.Count == 0)
            {
                dataTable.Columns.Add("ma_sp", typeof(string));
                dataTable.Columns.Add("ten_sp", typeof(string));
                dataTable.Columns.Add("gia_sp", typeof(int));
                dataTable.Columns.Add("nhacungcap_sp", typeof(string));
                dataTable.Columns.Add("hopdongso_sp", typeof(string));
                dataTable.Columns.Add("donvitinh_sp", typeof(string));
                dataTable.Columns.Add("soluong_sp", typeof(int));
                dataTable.Columns.Add("gioithieu_sp", typeof(string));
                dataTable.Columns.Add("soluong_cl", typeof(int));
                
            }

            //ma_sp, ten_sp, gioithieu_sp, soluong_sp, soluong_cl, gia_sp

            String workSheetNames;
            int i = 0;
            foreach (DataRow row in dataSet.Rows)
            {
                //insert the sheet's name in the current element of the array  
                //and remove the $ sign at the end  
                workSheetNames = row["TABLE_NAME"].ToString().Trim(new[] { '$' });

                DataTable dtable = GetWorksheet(workSheetNames);

                if (dtable != null)
                {
                    foreach (DataRow item in dtable.Rows)
                    {
                        dataTable.ImportRow(item);
                    }
                }

            }

            if (con != null)
            {
                con.Close();
                con.Dispose();
            }

            if (dataSet != null)
                dataSet.Dispose();

            return dataTable;
        }

        public System.Data.DataTable GetWorksheet(string worksheetName)
        {
            DataSet excelDataSet = new DataSet();
            try
            {
                OleDbConnection con = new System.Data.OleDb.OleDbConnection(connectionString);
                OleDbDataAdapter cmd = new System.Data.OleDb.OleDbDataAdapter(
                    "select ma_sp, ten_sp, gia_sp, nhacungcap_sp, hopdongso_sp, donvitinh_sp, soluong_sp, gioithieu_sp, soluong_cl from [" + worksheetName + "$]", con);

                con.Open();
                cmd.Fill(excelDataSet);
                con.Close();

                return excelDataSet.Tables[0];
            }
            catch(Exception ext)
            {
                return null;
            }
            
        }


        private void gridView1_FocusedRowChanged(object sender, DevExpress.XtraGrid.Views.Base.FocusedRowChangedEventArgs e)
        {
            rowSelect = e.FocusedRowHandle;
        }

        private void repositoryItemHyperLinkEdit1_Click(object sender, EventArgs e)
        {
            gridView1.DeleteRow(rowSelect);
            gcListSP.RefreshDataSource();
            gridView1.RefreshData();
        }

        private void btiTaiTepTin_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            openFileDialog.Filter = "xls (*.xls) | *.xls";
            openFileDialog.Title = "Sản phẩm";
            if (openFileDialog.ShowDialog() == DialogResult.OK)
            {
                DataTable dataTable = getData(openFileDialog.FileName);
                DataTable dt = (DataTable)gcListSP.DataSource;
                if (dt != null)
                {
                    foreach (DataRow row in dataTable.Rows)
                    {
                        dt.ImportRow(row);
                    }
                }
                else
                {
                    dt = dataTable;
                }
                gcListSP.DataSource = dt;
            }
        }

        private void btiLuuLai_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            SanPhamOB sp;

            DataTable dt = (DataTable)gcListSP.DataSource;
            

            int fail = 0;
            int succ = 0;

            if (dt != null)
            {
                if (dt.Rows.Count != 0)
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        if (row.RowState != DataRowState.Deleted)
                        {
                            sp = new SanPhamOB();

                            try
                            {
                                sp.ma_sp = row["ma_sp"].ToString();
                                sp.ten_sp = row["ten_sp"].ToString();
                                sp.soluong_sp = Int32.Parse(row["soluong_sp"].ToString());
                                sp.soluong_cl = Int32.Parse(row["soluong_cl"].ToString());
                                sp.gia_sp = Int32.Parse(row["gia_sp"].ToString());
                                sp.gioithieu_sp = row["gioithieu_sp"].ToString();
                                sp.nhacungcap_sp = row["nhacungcap_sp"].ToString();
                                sp.hopdongso_sp = row["hopdongso_sp"].ToString();
                                sp.donvitinh_sp = row["donvitinh_sp"].ToString();

                                spBUS.themSP(sp);

                                succ++;
                                
                            }
                            catch (Exception ext)
                            {
                                fail++;
                                continue;
                            }
                        }
                    }

                    //if (fail > 0)
                    //{
                    //    XtraMessageBox.Show("Hoàn thành quá trình thêm sản phẩm\n\t+ Thành công: " + succ + "\n\t+ Bị lỗi\t\t\t: " + fail, "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    //}

                    //return;
                }

                XtraMessageBox.Show("Hoàn thành quá trình thêm sản phẩm\n\t+ Thành công: " + succ + "\n\t+ Bị lỗi\t\t\t: " + fail, "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);

                //XtraMessageBox.Show("Thêm sản phẩm thành công", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
                if (fail == 0)
                {
                    gcListSP.DataSource = null;
                }

            }
            else
            {
                XtraMessageBox.Show("Không có thông tin về sản phẩm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

        private void btiHuyBo_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            gcListSP.DataSource = null;
        }

       
    }
}
